package sf.database.dialect.db;

import sf.core.DBField;
import sf.database.dbinfo.Feature;
import sf.database.dialect.DBDialect;
import sf.database.dialect.DBProperty;
import sf.database.dialect.Keywords;
import sf.database.meta.CascadeConfig;
import sf.database.meta.ColumnMapping;
import sf.database.meta.TableMapping;
import sf.database.support.DBMS;
import sf.jooq.JooqSupportDatabase;
import sf.querydsl.QueryDSLSupportDatabase;
import sf.tools.JavaTypeUtils;
import sf.tools.StringUtils;

import java.sql.Connection;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * sqlite
 */
public class SqliteDialect extends DBDialect {
    @Override
    public String getName() {
        return DBMS.sqlite.name();
    }

    @Override
    public int getNumber() {
        return DBMS.sqlite.getNumber();
    }

    public SqliteDialect() {
        this.keywords.addAll(Keywords.SQLITE);

        features.add(Feature.SUPPORT_CONCAT);
        features.add(Feature.AUTOINCREMENT_MUSTBE_PK);
        features.add(Feature.TYPE_FORWARD_ONLY);
        features.add(Feature.BATCH_GENERATED_KEY_ONLY_LAST);

        features.add(Feature.NOT_SUPPORT_TRUNCATE);
        features.add(Feature.NOT_SUPPORT_FOREIGN_KEY);
        features.add(Feature.NOT_SUPPORT_USER_FUNCTION);
        features.add(Feature.NOT_SUPPORT_SET_BINARY);
        features.add(Feature.NOT_SUPPORT_INDEX_META);
        features.add(Feature.NOT_SUPPORT_KEYWORD_DEFAULT);
        features.add(Feature.NOT_SUPPORT_ALTER_DROP_COLUMN);
        features.add(Feature.ONE_COLUMN_IN_SINGLE_DDL);

        setProperty(DBProperty.ADD_COLUMN, "ADD COLUMN");
        setProperty(DBProperty.MODIFY_COLUMN, "MODIFY COLUMN");
        setProperty(DBProperty.DROP_COLUMN, "DROP COLUMN");
        setProperty(DBProperty.CHECK_SQL, "select 1");
        setProperty(DBProperty.SELECT_EXPRESSION, "select %s");
        setProperty(DBProperty.WRAP_FOR_KEYWORD, "\"\"");
        setProperty(DBProperty.GET_IDENTITY_FUNCTION, "select last_insert_rowid()");
        setProperty(DBProperty.NVL_FUNCTION, "ifnull(%1$s,%2$s)");
    }

    @Override
    public String evalFieldType(ColumnMapping mf) {
        String type = "";
        int length = getColumnLength(mf);
        int scale = getColumnScale(mf);
        int precision = getColumnPrecision(mf);
        switch (mf.getSqlType()) {
            case Types.VARCHAR: {
                type = "NVARCHAR(" + length + ")";
                break;
            }
            case Types.TINYINT: {
                type = "SMALLINT";
                break;
            }
            case Types.SMALLINT: {
                type = "SMALLINT";
                break;
            }
            case Types.INTEGER: {
                type = "INT";
                break;
            }
            case Types.BIGINT: {
                type = "DECIMAL(8,0)";
                break;
            }
            case Types.FLOAT: {
                type = "FLOAT";
                break;
            }
            case Types.REAL:
            case Types.DOUBLE:
                type = "DOUBLE";
                break;
            case Types.BOOLEAN: {
                type = "TINYINT(1)";
                break;
            }
            case Types.DECIMAL: {
                // BigDecimal
                if (scale > 0 && precision > 0) {
                    type = "DECIMAL(" + precision + "," + scale + ")";
                } else {
                    throw new UnsupportedOperationException("Unsupport type!");
                }
                break;
            }
            case Types.NUMERIC: {
                if (precision > 0) {
                    type = "DECIMAL(" + precision + "," + scale + ")";
                } else {
                    type = "DECIMAL(255,0)";
                }
                break;
            }
            case Types.DATE:
                type = "DATE";
                break;
            case Types.TIME:
            case Types.TIME_WITH_TIMEZONE:
                type = "TIME";
                break;
            case Types.TIMESTAMP:
            case Types.TIMESTAMP_WITH_TIMEZONE:
                type = "TIMESTAMP";
                break;
            case Types.BLOB:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                type = "BLOB";
                break;
            case Types.CLOB:
            case Types.LONGVARCHAR:
            case Types.LONGNVARCHAR: {
                type = "TEXT";
                break;
            }
            default:
                type = super.evalFieldType(mf);
                break;
        }
        return type;
    }

    @Override
    public boolean createEntity(Connection conn, TableMapping en) {
        StringBuilder sb = new StringBuilder("CREATE TABLE " + wrapKeyword(en.getTableName()) + "(");
        // 创建字段
        boolean isAutoIncrement = false;
        boolean flag = false;
        for (Map.Entry<DBField, ColumnMapping> entry : en.getSchemaMap().entrySet()) {
            ColumnMapping cm = entry.getValue();
            if (cm.getJpaTransient() != null) {
                continue;
            }
            sb.append(flag ? "," : "").append(wrapKeyword(cm.getRawColumnName()));
            // Sqlite的整数型主键,一般都是自增的,必须定义为(PRIMARY KEY
            // AUTOINCREMENT),但这样就无法定义多主键!!
            if (cm.isPk() && cm.getGv() != null && JavaTypeUtils.isNumberClass(cm.getClz())) {
                sb.append(" INTEGER PRIMARY KEY AUTOINCREMENT");
                isAutoIncrement = true;
            } else {
                sb.append(' ').append(evalFieldDefinition(cm));
            }
            flag = true;
        }
        // 创建主键
        if (!isAutoIncrement) {
            List<ColumnMapping> pks = en.getPkFields();
            if (!pks.isEmpty()) {
                sb.append(" ,PRIMARY KEY (");
                boolean f = false;
                for (ColumnMapping pk : pks) {
                    sb.append(f ? "," : "").append(wrapKeyword(pk.getRawColumnName()));
                    f = true;
                }
                sb.append(") ");
            }
        }

        // 结束表字段设置
        sb.append(")");
        // 设置特殊引擎
//		if (en.hasMeta(META_ENGINE)) {
//			sb.append(" ENGINE=" + en.getMeta(META_ENGINE));
//		}
        // 默认采用 UTF-8 编码

        sb.append(" ");

        List<String> sqls = new ArrayList<>();
        // 执行创建语句
        sqls.add(sb.toString());
        // 创建索引
        sqls.addAll(createIndexSql(en));
        //创建约束
        sqls.addAll(createUniqueSql(en));

        execute(sqls, conn);

        // 创建关联表
        createRelation(conn, en);
        return true;
    }

    @Override
    public String uniqueSql(String tableName, String name, String[] columnNames) {
        return null;
    }

    protected List<String> createRelation(Connection conn, ColumnMapping en) {
        List<String> sqls = new ArrayList<>();
        List<String> columns = new ArrayList<>();
        CascadeConfig cc = en.getCascadeConfig();
        if (existsTables(conn, cc.getMiddleTableName())) {
            return sqls;
        }
        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE ").append(wrapKeyword(cc.getMiddleTableName())).append("(");
        int count = cc.getMiddleTableColumns().size();

        for (Map.Entry<String, ColumnMapping> entry : cc.getMiddleTableColumns().entrySet()) {
            String key = entry.getKey();
            key = wrapKeyword(key);
            columns.add(key);
            sb.append(key).append(" ").append(evalFieldType(entry.getValue())).append(",");
        }
        sb.append("constraint pk_").append(cc.getMiddleTableName()).append(" PRIMARY KEY (");
        boolean f = false;
        for (String column : columns) {
            sb.append(f ? "," : "").append(column);
            f = true;
        }
        sb.append(") )");
        sqls.add(sb.toString());
        return sqls;
    }

    @Override
    protected String escapeSqlValue(String value) {
        return StringUtils.replace(value, "'", "\\'");
    }

    @Override
    public String sqlTableDrop(String table) {
        return String.format("drop table if exists %s;", wrapKeyword(table));
    }

    @Override
    public String sqlTableRename(String oldName, String newName) {
        return String.format("rename table  %s to %s;", wrapKeyword(oldName), wrapKeyword(newName));
    }

    @Override
    public String sqlColumnRename(String table, String oldColumnName, String newColumnName) {
        return null;
    }

    @Override
    public String sqlColumnAdd(String table, String column_definition, String column_position) {
        String sql = String.format("alter table %s add %s", wrapKeyword(table), column_definition);
        if (supportsColumnPosition() && column_position != null) {
            sql = sql + " " + column_position;
        }
        return sql + ";";
    }

    @Override
    public String sqlColumnModify(String table, String column_definition, String column_position) {
        String sql = String.format("alter table %s modify %s", wrapKeyword(table), column_definition);
        if (supportsColumnPosition() && column_position != null) {
            sql = sql + " " + column_position;
        }
        return sql + ";";
    }

    @Override
    public String sqlColumnDrop(String table, String column) {
        return String.format("alter table %s drop %s;", wrapKeyword(table), wrapKeyword(column));
    }

    @Override
    public StringBuilder sqlPageList(StringBuilder sql, long offset, int limit) {
        if (offset > 0) {
            return sql.append(" limit ").append(limit).append(" offset ").append(offset);
        } else {
            return sql.append(" limit ").append(limit);
        }
    }

    @Override
    public boolean supportsColumnPosition() {
        return true;
    }

    @Override
    public String getHibernateDialect() {
        return "";
    }

    @Override
    public String getQueryDslDialect() {
        return QueryDSLSupportDatabase.SQLite;
    }

    @Override
    public String getJooqDialect() {
        return JooqSupportDatabase.SQLITE;
    }
}
